package org.example.lethear.repository;

import org.example.lethear.entity.Category;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

/**
 * 分类数据访问接口
 */
@Repository
public interface CategoryRepository extends JpaRepository<Category, Long> {
    
    /**
     * 根据分类名查找分类
     */
    Optional<Category> findByName(String name);
    
    /**
     * 检查分类名是否存在
     */
    boolean existsByName(String name);
    
    /**
     * 根据父分类ID查找子分类
     */
    List<Category> findByParentId(Long parentId);
    
    /**
     * 根据父分类ID和状态查找子分类
     */
    List<Category> findByParentIdAndStatus(Long parentId, Integer status);
    
    /**
     * 查找所有顶级分类（父分类ID为0或null）
     */
    @Query("SELECT c FROM Category c WHERE c.parentId = 0 OR c.parentId IS NULL")
    List<Category> findTopLevelCategories();
    
    /**
     * 查找启用的顶级分类
     */
    @Query("SELECT c FROM Category c WHERE (c.parentId = 0 OR c.parentId IS NULL) AND c.status = 1 ORDER BY c.sortOrder ASC")
    List<Category> findEnabledTopLevelCategories();
    
    /**
     * 根据状态查找分类
     */
    List<Category> findByStatus(Integer status);
    
    /**
     * 根据状态分页查找分类
     */
    Page<Category> findByStatus(Integer status, Pageable pageable);
    
    /**
     * 模糊搜索分类名
     */
    @Query("SELECT c FROM Category c WHERE c.name LIKE %:keyword% OR c.description LIKE %:keyword%")
    Page<Category> searchCategories(@Param("keyword") String keyword, Pageable pageable);
    
    /**
     * 根据排序顺序查找分类
     */
    @Query("SELECT c FROM Category c WHERE c.status = 1 ORDER BY c.sortOrder ASC")
    List<Category> findEnabledCategoriesOrderBySortOrder();
    
    /**
     * 查找某个分类下的所有子分类（递归）
     */
    @Query("SELECT c FROM Category c WHERE c.parentId = :parentId AND c.status = 1")
    List<Category> findEnabledChildCategories(@Param("parentId") Long parentId);
    
    /**
     * 统计分类总数
     */
    @Query("SELECT COUNT(c) FROM Category c WHERE c.status = 1")
    long countEnabledCategories();
    
    /**
     * 统计顶级分类数量
     */
    @Query("SELECT COUNT(c) FROM Category c WHERE (c.parentId = 0 OR c.parentId IS NULL) AND c.status = 1")
    long countTopLevelCategories();
    
    /**
     * 根据父分类统计子分类数量
     */
    @Query("SELECT COUNT(c) FROM Category c WHERE c.parentId = :parentId AND c.status = 1")
    long countChildCategories(@Param("parentId") Long parentId);
    
    /**
     * 查找分类树（包含帖子数量）
     */
    @Query("SELECT c, COUNT(p) FROM Category c LEFT JOIN Post p ON c.id = p.categoryId WHERE c.status = 1 GROUP BY c.id ORDER BY c.sortOrder ASC")
    List<Object[]> findCategoriesWithPostCount();
} 